<!doctype html>
<html lang="zh-cn">
  <head>
    <title>MySQL数据库的高级使用 // JW Blog</title>
    <meta charset="utf-8" />
    <meta name="generator" content="Hugo 0.60.1" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <meta name="author" content="John Doe" />
    <meta name="description" content="" />
    <link rel="stylesheet" href="https://duyi111.gitee.io/css/main.min.f90f5edd436ec7b74ad05479a05705770306911f721193e7845948fb07fe1335.css" />

    
    <meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="MySQL数据库的高级使用"/>
<meta name="twitter:description" content="1.将查询结果插入到其他表中  想要完成表复制可以使用： insert into .. select .. SQL语句   2.使用连接更新表中某个字段数据  连接更新表中数据使用： update ..join .. SQL语句   3.创建表并给某个字段添加数据  创建表并给字段插入数据使用： create table .. select ..SQL语句   4.修改表结构  修改表结构可以使用： alter table 表名 change ..   5.PyMySQL的使用 5.1 python程序操作MySQL数据库  安装pymysql第三方包 sudo pip3 install pymysql  安装命令使用sudo pip3 install 第三方名 卸载命令使用sudo pip3 uninstall 第三方包名 pip3 list查看使用pip命令安装的第三方包列表   pymysql的使用：  1.导入pymysql包 import pymysql  2.创建连接对象 # 调用pymysql模块中的connect()函数来创建连接对象 conn = pymysql."/>

    <meta property="og:title" content="MySQL数据库的高级使用" />
<meta property="og:description" content="1.将查询结果插入到其他表中  想要完成表复制可以使用： insert into .. select .. SQL语句   2.使用连接更新表中某个字段数据  连接更新表中数据使用： update ..join .. SQL语句   3.创建表并给某个字段添加数据  创建表并给字段插入数据使用： create table .. select ..SQL语句   4.修改表结构  修改表结构可以使用： alter table 表名 change ..   5.PyMySQL的使用 5.1 python程序操作MySQL数据库  安装pymysql第三方包 sudo pip3 install pymysql  安装命令使用sudo pip3 install 第三方名 卸载命令使用sudo pip3 uninstall 第三方包名 pip3 list查看使用pip命令安装的第三方包列表   pymysql的使用：  1.导入pymysql包 import pymysql  2.创建连接对象 # 调用pymysql模块中的connect()函数来创建连接对象 conn = pymysql." />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://duyi111.gitee.io/post/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E9%AB%98%E7%BA%A7%E4%BD%BF%E7%94%A8/" />
<meta property="article:published_time" content="2020-02-04T17:55:03+08:00" />
<meta property="article:modified_time" content="2020-02-04T17:55:03+08:00" />


  </head>
  <body>
    <header class="app-header">
      <a href="https://duyi111.gitee.io/"><img class="app-header-avatar" src="/avatar.jpg" alt="John Doe" /></a>
      <h1>JW Blog</h1>
      <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc vehicula turpis sit amet elit pretium.</p>
      <div class="app-header-social">
        
      </div>
    </header>
    <main class="app-container">
      
  <article class="post">
    <header class="post-header">
      <h1 class ="post-title">MySQL数据库的高级使用</h1>
      <div class="post-meta">
        <div>
          <svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="icon icon-calendar">
  <title>calendar</title>
  <rect x="3" y="4" width="18" height="18" rx="2" ry="2"></rect><line x1="16" y1="2" x2="16" y2="6"></line><line x1="8" y1="2" x2="8" y2="6"></line><line x1="3" y1="10" x2="21" y2="10"></line>
</svg>
          Feb 4, 2020
        </div>
        <div>
          <svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="icon icon-clock">
  <title>clock</title>
  <circle cx="12" cy="12" r="10"></circle><polyline points="12 6 12 12 16 14"></polyline>
</svg>
          2 min read
        </div></div>
    </header>
    <div class="post-content">
      <h2 id="1">1.将查询结果插入到其他表中</h2>
<ul>
<li>想要完成表复制可以使用：
<pre><code>insert into .. select .. SQL语句
</code></pre></li>
</ul>
<h2 id="2">2.使用连接更新表中某个字段数据</h2>
<ul>
<li>连接更新表中数据使用：
<pre><code>update ..join .. SQL语句
</code></pre></li>
</ul>
<h2 id="3">3.创建表并给某个字段添加数据</h2>
<ul>
<li>创建表并给字段插入数据使用：
<pre><code>create table .. select ..SQL语句
</code></pre></li>
</ul>
<h2 id="4">4.修改表结构</h2>
<ul>
<li>修改表结构可以使用：
<pre><code>alter table 表名 change ..
</code></pre></li>
</ul>
<h2 id="5pymysql">5.PyMySQL的使用</h2>
<h4 id="51-pythonmysql">5.1 python程序操作MySQL数据库</h4>
<ul>
<li>安装pymysql第三方包
<pre><code>sudo pip3 install pymysql
</code></pre><ul>
<li>安装命令使用<code>sudo pip3 install 第三方名</code></li>
<li>卸载命令使用<code>sudo pip3 uninstall 第三方包名</code></li>
<li><code>pip3 list</code>查看使用pip命令安装的第三方包列表</li>
</ul>
</li>
<li>pymysql的使用：
<ul>
<li>1.导入pymysql包
<pre><code>import pymysql
</code></pre></li>
<li>2.创建连接对象
<pre><code># 调用pymysql模块中的connect()函数来创建连接对象
conn = pymysql.connect(参数列表)
</code></pre><ul>
<li>参数<code>host</code>：连接的mysql主机，如果本机是<code>localhost</code></li>
<li>参数<code>port</code>：连接的mysql主机的端口，默认是3306</li>
<li>参数<code>user</code>：拦击连接的用户名</li>
<li>参数<code>password</code>：连接的密码</li>
<li>参数<code>database</code>：数据库的名称</li>
<li>参数<code>charset</code>：通信采用的编码方式，推荐使用utf8</li>
<li><em><strong>连接对象操作说明：</strong></em>
<ul>
<li>关闭连接conn.close()</li>
<li>提交数据conn.commit()</li>
<li>撤销数据conn.rollback()</li>
</ul>
</li>
</ul>
</li>
<li>3.获取游标对象
<pre><code># 调用连接对象的cursor()方法获取游标对象
cur = conn.cuesor()
</code></pre><ul>
<li>获取游标对象的目标就是要执行SQL语句，完成对数据库的增删改查操作</li>
<li>游标操作说明：
<ul>
<li>使用游标执行SQL语句：<code>execute(operation [parameters ]) </code>执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete、select等语句</li>
<li>获取查询结果集中的一条数据：<code>cur.fetchone()</code>返回一个元组,如：(1,&lsquo;张三&rsquo;)</li>
<li>获取查询结果集中的所有数据：<code>cur.fetchall()</code>返回一个元组，如((1,&lsquo;张三&rsquo;),(2,&lsquo;李四&rsquo;))</li>
<li>关闭游标：<code>cur.close()</code>,表示和数据库操作完成</li>
</ul>
</li>
</ul>
</li>
<li>4.pymysql完成数据的查询操作
<pre><code>import pymysql
# 创建连接对象
conn = pymysql.connect(host='localhost', port=3306, user='root',password='',database='',charset='utf8')
# 获取游标对象
cursor = conn.cursor()
try:
    # 添加  SQL语句
    # sql = 'insert into students(name) values('小明'),('小刚');'
    # 删除  SQL语句
    # sql = 'delete from students where id = 5;'
    # 修改  SQL语句
    sql = 'update students set name = '铁蛋' where id = 6;'
    # 执行SQL语句
    row_count = cursor.execute(sql)
    # 提交数据到数据库
    conn.commit()
except Exception as e:
    # 回滚数据，即撤销刚刚的SQL语句操作
    conn.rollback()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
</code></pre><ul>
<li>conn.commit()表示将修改操作提交到数据库</li>
<li>conn.rollback()表示回滚数据</li>
</ul>
</li>
</ul>
</li>
</ul>
<h4 id="52-sql">5.2 防止SQL注入</h4>
<ul>
<li>1.概念：
<ul>
<li>用户提交带有恶意的数据与SQL语句进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象。</li>
</ul>
</li>
<li>2.如何防止SQL注入：
<ul>
<li>SQL语句中的参数使用%s来占位，此处不是python中的字符串格式化操作</li>
<li>将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数</li>
</ul>
</li>
<li>3.防止SQL注入的示例代码：
<pre><code>
</code></pre></li>
</ul>
<h4 id="53-">5.3 小结</h4>
<ul>
<li>1.导包
<pre><code>import pymysql
</code></pre></li>
<li>2.创建连接对象
<pre><code>pymysql.connect(参数列表)
</code></pre></li>
<li>3.获取游标对象
<pre><code>cursor = conn.cursor()
</code></pre></li>
<li>4.执行SQL语句
<pre><code>row_count = cursor.execute(sql)
</code></pre></li>
<li>5.获取查询结果集
<pre><code>result = cursor.fetchall()
</code></pre></li>
<li>6.将修改操作提交到数据库
<pre><code>conn.commit()
</code></pre></li>
<li>7.回滚数据
<pre><code>conn.rollback()
</code></pre></li>
<li>8.关闭游标
<pre><code>cursor.close()
</code></pre></li>
<li>9.关闭连接
<pre><code>conn.close()
</code></pre></li>
</ul>
<h2 id="6">6.事务</h2>
<h4 id="61-">6.1 事务的介绍</h4>
<ul>
<li>事务就是用户定义的一系列执行SQL语句的操作，这些操作要么完全地执行，要么完全地都不执行，它是一个不可分割的工作执行单元</li>
</ul>
<h4 id="62-">6.2 事务的四大特性</h4>
<ul>
<li>原子性
<ul>
<li>一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分呢操作，这就是事务的原子性</li>
</ul>
</li>
<li>一致性
<ul>
<li>数据库总是从一个一致性的状态转换到另一个一致性的状态</li>
</ul>
</li>
<li>隔离性
<ul>
<li>通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的</li>
</ul>
</li>
<li>持久性
<ul>
<li>一旦事务提交，则其所作的修改会永久保存到数据库。</li>
</ul>
</li>
<li>说明：
<ul>
<li>事务能够保证数据的完整性和一致性，让用户的操作更加安全</li>
</ul>
</li>
</ul>
<h4 id="63-">6.3 事务的使用</h4>
<ul>
<li>在使用事务之前，先要确保表的存储引擎是InnoDB类型，只有这个类型才可以使用事务，MySQL数据库中表的存储引擎默认是InnoDB类型</li>
<li>表的存储引擎说明:
<ul>
<li>表的存储引擎就是提供存储数据一种机制，不同表的存储引擎提供不同的存储机制</li>
</ul>
</li>
<li>查看MySQL数据库支持的表的存储引擎：
<pre><code># 查看MySQL数据库支持的表的存储引擎
show engines;
  
# 修改表的存储引擎
alter table 表名 engine = 引擎类型;
</code></pre><ul>
<li>说明：
<ul>
<li>常用的表的存储引擎是<code>InnoDB</code>和<code>MylSAm</code></li>
<li><code>InnoDB</code>是支持事务的</li>
<li><code>MylSAM</code>不支持事务，优势是访问速度快。对事务没有要求或者以<code>select、insert</code>为主的都可以使用该存储引擎来创建表</li>
</ul>
</li>
</ul>
</li>
<li><em><strong>开启事务：</strong></em>
<pre><code>bigin;
或者
start transaction;
</code></pre><ul>
<li>说明：
<ul>
<li>开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中</li>
<li>MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务，那么每条SQL语句都会被当作一个事务执行提交的操作</li>
<li>当设置<code>autocommit=0</code>表示取消自动提交事务模式，需要手动执行<code>commit</code>完成事务的提交
<pre><code>set autocommit = 0;
insert into students(name) values('刘三峰');
# 需要执行手动提交，数据才会真正添加到表中, 验证的话需要重新打开一个连接窗口查看表的数据信息，因为是临时关闭自动提交模式
commit

# 重新打开一个终端窗口，连接MySQL数据库服务端
mysql -uroot -p

# 然后查询数据,如果上个窗口执行了commit，这个窗口才能看到数据
select * from students;
</code></pre></li>
</ul>
</li>
</ul>
</li>
<li><em><strong>提交事务：</strong></em>
<pre><code>commit;
</code></pre></li>
<li><em><strong>回滚事务：</strong></em>
<pre><code>rollback;
</code></pre></li>
</ul>
<h4 id="64-">6.4 小结</h4>
<ul>
<li>1.事务的特性：
<ul>
<li>原子性：强调事务中的多个操作是一个整体</li>
<li>一致性：强调数据库中不会保存不一致状态</li>
<li>隔离性：强调数据库中事务之间相互不可见</li>
<li>持久性：强调数据库能永久保存数据，一旦提交就不可撤销</li>
</ul>
</li>
<li>2.<em><strong>MySQL数据库默认采用自动提交(autocommit)模式</strong></em>，也就是说修改数据的操作会自动的触发事务，完成事务的提交或者回滚</li>
<li>3.开启事务使用<code>begin</code>或者<code>start transaction;</code></li>
<li>4.回滚事务使用<code>rollback;</code></li>
<li>5.pymysql里面的<code>conn.commit()</code>操作就是提交事务</li>
<li>6.pymysql里面的<code>conn.rollback()</code>操作就是回滚事务</li>
</ul>
<h2 id="7">7.索引</h2>
<h4 id="71-">7.1 索引的介绍</h4>
<ul>
<li>索引在MySQL中也叫作“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。</li>
<li>当数据库中数据量很大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。</li>
</ul>
<h4 id="72-">7.2 索引的使用</h4>
<ul>
<li>查看表中已有索引：
<pre><code>show index from 表名;
</code></pre><ul>
<li>说明：
<ul>
<li>主键列会自动创建索引</li>
</ul>
</li>
</ul>
</li>
<li>索引的创建
<pre><code># 创建索引的语法格式
alter table 表名 add index 索引名[可选](列名, ..)
</code></pre><ul>
<li>索引名不指定，默认使用字段名</li>
</ul>
</li>
<li>索引的删除
<pre><code># 删除索引的语法格式
alter table 表名 drop index 索引名
# 如果不知道索引名，可以查看创表SQL语句
show create table classes;
</code></pre></li>
</ul>
<h4 id="73-">7.3 联合索引</h4>
<ul>
<li>联合索引又叫做符合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。
<pre><code># 创建联合索引
alter table 表名 add index (字段1, 字段2, ..)
</code></pre></li>
<li>联合索引的好处：
<ul>
<li>减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销</li>
</ul>
</li>
</ul>
<h4 id="74">7.4联合索引的最左原则</h4>
<ul>
<li>在使用联合索引的时候，我们遵守一个最左原则，即index(name, age)支持name、name和age组合查询，而不支持单独age查询，因为没有用到创建的联合索引</li>
<li>在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面，否则联合索引失效</li>
</ul>
<h4 id="75-mysql">7.5 MySQL中索引的优点和缺点和使用原则</h4>
<ul>
<li>优点：
<ul>
<li>加快数据的查询速度</li>
</ul>
</li>
<li>缺点：
<ul>
<li>创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加</li>
</ul>
</li>
<li>使用原则：
<ul>
<li>1.通过优缺点对比，不是索引越多越好，而是需要自己合理的使用</li>
<li>2.对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引</li>
<li>3.数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的事件比遍历索引的时间还要短，索引就可能不会产生优化效果</li>
<li>4.在一字段上相同值比较多不要建立索引，相反的在一个字段是上不同值比较多可以建立索引</li>
</ul>
</li>
</ul>
<h4 id="76-">7.6 小结</h4>
<ul>
<li>索引是加快数据库的查询速度的一种手段</li>
<li>创建索引：
<pre><code>alter table 表名 add index 索引名[可选](字段名,..);
  
</code></pre></li>
<li>删除索引：
<pre><code>alter table 表名 drop index 索引名;
</code></pre></li>
</ul>

    </div>
    <div class="post-footer">
      
    </div>
  </article>

    </main>
  </body>
</html>
